返回首页

Oracle 锁表排查及解锁 SQL

本文档记录了 Oracle 数据库中锁表排查及解锁的常用 SQL 语句,涵盖临时表锁处理、对象锁查询及 Kill 会话等场景。

一、处理 ORA-14450 临时表被占用并解锁

1.1 查询持有临时表锁的会话

SELECT SID, SERIAL# FROM V$session WHERE SID IN (SELECT sid FROM v$enqueue_lock t WHERE t.type = 'TO');

1.2 生成 Kill 会话语句

SELECT 'alter system kill session ''' || a.sid || ',' || a.SERIAL# || ''';'
FROM V$session a
WHERE SID IN (SELECT SID FROM v$enqueue_lock t WHERE t.type = 'TO');

二、查询锁表并生成解锁语句

2.1 按表名查询锁表会话

SELECT 'alter system kill session ''' || a.sid || ',' || c.serial# || ''';'
FROM v$lock a, dba_objects b, v$session c
WHERE a.id1 = b.object_id
  AND a.sid = c.sid
  AND b.object_name = UPPER('ORDERRETAIL_RP_RETAIL004');

三、解决 ORA-14450:试图访问已经在使用的事务处理临时表

3.1 查询对象信息

SELECT * FROM dba_objects WHERE object_name = 'RP_STORE_DAILYBALANCE_GEN';

3.2 按对象 ID 查询锁并生成解锁语句

SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';'
FROM v$session
WHERE sid IN (SELECT sid
              FROM v$enqueue_lock t
              WHERE t.type = 'TO'
                AND ID1 = '132733');

3.3 通用:解决 ORA-14450 临时表被占用

SELECT 'alter system kill session ''' || a.sid || ',' || a.SERIAL# || ''';'
FROM V$session a
WHERE SID IN (SELECT SID FROM v$enqueue_lock t WHERE t.type = 'TO');